package org.jgs1904.dao;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.ArrayHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.jgs1904.entity.Teacher;
import org.jgs1904.util.DataSourceUtil;

/**
 * Apache Commons DbUtils
 * <p>
 * 1.QueryRunner类使用
 * query()：查询
 * update()：更新（插入、删除、修改）
 * insert()：插入（返回主键）
 * <p>
 * 2.ResultSetHandler接口
 * ArrayHandler：把结果集中的第一行数据存放到数组
 * ArrayListHandler：把结果集中的每一行数据存放到数组集合
 * BeanHandler：把结果集中的第一行数据封装到指定的实体类中
 * BeanListHandler：把结果集中的每一行数据封装到指定的实体类集合中
 * ColumnListHandler：把结果集中的某一列数据存放到list集合中
 * ScalarHandler：一般用于获取聚合函数的统计结果
 *
 * @author dyf
 * @date 2020年2月3日
 */
public class TeacherDaoForDBUtils {

    public List<Teacher> selectAll() throws SQLException {

        // 使用数据源作为参数创建QueryRunner对象
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        // 使用query方法实现查询
        String sql = "select * from teacher";
        List<Teacher> teacherList = qr.query(sql, new BeanListHandler<>(Teacher.class));

        return teacherList;
    }

    public Teacher selectOne() throws SQLException {

        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from teacher limit 0,1";
        Teacher result = qr.query(sql, new BeanHandler<>(Teacher.class));

        return result;
    }

    // 统计有多少老师
    public long selectCount() throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select count(*) from teacher";

        Object count = qr.query(sql, new ScalarHandler<>(2));

        return (Long) count;
    }

    // 多条件查询
    public List<Teacher> selectAll(Teacher teacher) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "select * from teacher where 1 = 1";
        List<Object> params = new ArrayList<>();

        if (teacher.getId() != null) {
            sql += " and id = ?";
            params.add(teacher.getId());
        }
        if (teacher.getName() != null) {
            sql += " and name = ?";
            params.add(teacher.getName());
        }
        if (teacher.getSex() != null) {
            sql += " and sex = ?";
            params.add(teacher.getSex());
        }
        if (teacher.getClass_id() != null) {
            sql += " and class_id = ?";
            params.add(teacher.getClass_id());
        }

        //第三个参数是可变参 也可以用数组
        List<Teacher> result = qr.query(sql, new BeanListHandler<>(Teacher.class), params.toArray());

        return result;
    }

//	public int insert(Teacher teacher) throws SQLException {
//		QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());
//		
//		String sql = "insert into teacher(name,sex,birthday,class_id) value(?,?,?,?)";
//		
//		int result = qr.update(sql, teacher.getName(), teacher.getSex(), teacher.getBirthday(), teacher.getClass_id());
//		
//		return result;
//	}

    public Teacher insert(Teacher teacher) throws SQLException {
        QueryRunner qr = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = "insert into teacher(name,sex,birthday,class_id) value(?,?,?,?)";

        // 保存数据并实现主键返回，注意：返回的主键是BigInteger类型
        BigInteger id = (BigInteger) qr.insert(sql, new ScalarHandler<>(1), teacher.getName(), teacher.getSex(), teacher.getBirthday(), teacher.getClass_id());

        teacher.setId(id.longValue());

        return teacher;
    }

}
